﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Reporting
{
    public class Database
    {
        string cnnString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

        public List<string> GetViews()
        {
            List<string> view = new List<string>();
            using (SqlConnection cn = new SqlConnection(cnnString))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("select TABLE_NAME from INFORMATION_SCHEMA.VIEWS", cn))
                {
                    SqlDataReader rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        while (rd.Read())
                        {
                            view.Add(rd.GetString(0));
                        }
                    }
                    rd.Close();
                }
                cn.Close();
            }
            return view;
        }

        public List<Column> GetViewsColumns(string viewName, ref bool parametreli)
        {
            List<Column> view = new List<Column>();
            using (SqlConnection cn = new SqlConnection(cnnString))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(string.Format("select column_name, data_type from INFORMATION_SCHEMA.COLUMNS where table_name = '{0}'", viewName), cn))
                {
                    SqlDataReader rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        while (rd.Read())
                        {
                            if (rd.GetString(0) == "PARAMETRE")
                            {
                                parametreli = true;
                            }
                            else
                                view.Add(new Column() { Name = rd.GetString(0), Type = Column.GetType(rd.GetString(1)) });
                        }
                    }
                    rd.Close();
                }
                cn.Close();
            }
            return view;
        }

        public DataTable LoadDt(string sqlTxt)
        {
            DataTable dt = new DataTable();
            using (SqlConnection cn = new SqlConnection(cnnString))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(sqlTxt, cn))
                {
                    using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                    {
                        adp.Fill(dt);
                    }
                }
                cn.Close();
            }
            return dt;
        }

    }

    public class Column
    {
        public string Name { get; set; }
        public DbType Type { get; set; }
        public static DbType GetType(string type)
        {
            switch (type)
            {
                case "int":
                    return DbType.Int32;
                case "varchar":
                case "nvarchar":
                    return DbType.String;
                case "datetime":
                    return DbType.DateTime;
                case "float":
                case "money":
                case "decimal":
                    return DbType.Decimal;
                case "smallint":
                    return DbType.Int16;
                case "bit":
                    return DbType.Boolean;
                default:
                    return DbType.String;
            }
        }
    }
}
